import sys
import pickle
import datetime

class Expense:
    def __init__(self, TexID, EID, PID, Date, Desc, Amount):
        self.TexID = TexID
        self.EID = EID
        self.PID = PID
        self.Date = Date
        self.Desc = Desc
        self.Amount = Amount

def FormatTimeDateToString(DateTimeObj):
    STR = """%s, %s, %s"""%(str(DateTimeObj.year), str(DateTimeObj.month), str(DateTimeObj.day))
    return STR

def FormatStringtoTimeDate(String):
    Numbers = String.split(',')
    return datetime.date(int(Numbers[0]), int(Numbers[1]), int(Numbers[2]))
    

class TripExpenses:
    def __init__(self, SQLConn, dBName = "TripExpenses"):
        self.SQLConn = SQLConn
        self.TripExpensesdBName = dBName
        
        
        
    def AddExpense(self, EID, PID, Date, Disc, Amount):
        self.SQLConn.SendCommand("""
         INSERT INTO %s (Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription)
         VALUES ('%d', '%d', '%.2f', '%s', '%s')
       """ % (self.TripExpensesdBName, EID, PID, Amount, FormatTimeDateToString(Date), Disc))
        print Amount
    def RemoveExpense(self, Texp_id):
        self.SQLConn.SendCommand("""
         DELETE FROM %s where TExp_id=%d
         """ % (self.TripExpensesdBName, Texp_id))
        
    def GetAllExpenses(self):
        self.SQLConn.SendCommand("""SELECT TExp_id, Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription FROM %s"""%self.TripExpensesdBName)
        rows = self.SQLConn.FetchAll ()
        Expenses = []
        for Exp in rows:
           Expenses.append(Expense(int(Exp[0]), int(Exp[1]), int(Exp[2]), float(Exp[3]), FormatStringtoTimeDate(Exp[4]), Exp[5]))
        return Expenses
    
    def GetAllExpensesDict(self):
        self.SQLConn.SendCommand("""SELECT TExp_id, Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription FROM %s"""%self.TripExpensesdBName)
        rows = self.SQLConn.FetchAll ()
        Expenses = {}
        for Exp in rows:
           Expenses[int(Exp[0])] = Expense(int(Exp[0]), int(Exp[1]), int(Exp[2]), float(Exp[3]), FormatStringtoTimeDate(Exp[4]), Exp[5])
        return Expenses
    
    def UpdateExpense(self, Expense):
        self.SQLConn.SendCommand("""UPDATE %s
                                SET Eve_id = '%d',
                                Per_Owed_Id = '%d',
                                Amount = '%f',
                                Exp_Date = '%s',
                                Exp_Discription= '%s'
                                WHERE TExp_id = %d
                                """ % (self.TripExpensesdBName, Expense.EID, Expense.PID, Expense.Amount, FormatTimeDateToString(Expense.Date), Expense.Desc, Expense.TexID))
       
    def GetExpenses(self, TexId = -1, EID=-1, PID = -1, Amount = -1, Date = -1, Disc = -1):
        First = True
        str = """WHERE """
        if Disc != -1:
            if First:
                First = False
                str = str + """Exp_Discription = '%s' """ % Disc
            else:
                str = str + """AND Exp_Discription = '%s' """ % Disc
        if Date != -1:
            if First:
                First = False
                str = str + """Exp_Date = '%s' """ % FormatTimeDateToString(Date)
            else:
                str = str + """AND Exp_Date = '%s' """ % FormatTimeDateToString(Date)
        if Amount != -1:
            if First:
                First = False
                str = str + """Amount = '%.2f' """ % Amount
            else:
                str = str + """AND Amount = '%.2f' """ % Amount
        if TexId != -1:
            if First:
                First = False
                str = str + """TExp_id = '%d' """ % TexId
            else:
                str = str + """AND TExp_id = '%d' """ % TexId
        if PID != -1:
            if First:
                First = False
                str = str + """Per_Owed_Id = %d """ % PID
            else:
                str = str + """AND Per_Owed_Id = '%d' """ % PID
        if EID != -1:
            if First:
                First = False
                str = str + """Eve_id = %d """ % EID
            else:
                str = str + """AND Eve_id = '%d' """ % EID
        
        self.SQLConn.SendCommand("""SELECT TExp_id, Eve_id, Per_Owed_Id, Exp_Date, Exp_Discription, Amount FROM %s """%self.TripExpensesdBName + str)
        Data = self.SQLConn.FetchAll ()
        Res = []
        if len(Data) >  1:
            Res = []
            for Exp in Data:
                Res.append(Expense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[3]), Exp[4], float(Exp[5])))
            
        elif len(Data) == 1:
            Exp = Data[0]
            Res = [Expense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[3]), Exp[4], float(Exp[5]))]

            
        return Res
    

    def GetTotalSpentByPersonOnEvent(self,PID,EID):
        Exps = self.GetExpenses(PID = PID, EID = EID)
        Amount = 0.0
        if Exps != None:
            for Exp in Exps:
                Amount = Amount + Exp.Amount
        #else:
            #Amount = Exp.Amount
        return Amount
    
    def GetTotalEventCost(self, EID):
        Exps = self.GetExpenses(EID = EID)
        Amount = 0.0
        if Exps != None:
            for Exp in Exps:
                Amount = Amount + Exp.Amount
        #else:
            #Amount = Exp.Amount
        return Amount
        
        
            
    
    
def GenerateTables(SQL):
    print """Generating "TripExpenses" Table"""
    
    SQL.SendCommand ("DROP TABLE IF EXISTS TripExpenses")
    SQL.SendCommand("""CREATE TABLE TripExpenses(
                    TExp_id int unsigned not null auto_increment primary key,
                    Eve_id int,
                    Per_Owed_Id int,
                    Amount float,
                    Exp_Date BLOB,
                    Exp_Discription varchar(1000)
                    )
                    """)

    
  
    
        